﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using UncleBobWebService.Models;
using UncleBobWebService.ServiceRequester;


namespace UncleBobWebService.Database
{
    class StaffDBAdapter : IDBAdapter
    {

        #region "Overrides"

        public override Message CreateData(Message msg, object data)
        {
            mCommand = mConnection.CreateCommand();

            mCommand.CommandText = string.Format("INSERT INTO Jobs (JobTitle, JobLocation, JobSalary, JobDescription, PositionsAvailable, Active) VALUES ('{0}', '{1}', '{2}', '{3}', {4}, '{5}')"             
                    , ((JobModel)data).Title
                    , ((JobModel)data).Location
                    , ((JobModel)data).Salary
                    , ((JobModel)data).Description
                    , ((JobModel)data).PositionsAvailable
                    , true);

            try
            {
                mConnection.Open();
                mCommand.ExecuteNonQuery();
                mConnection.Close();
                msg.Result = true;
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;
        }


        public override Message SearchForSpecificData(Message msg, Object data)
        {           
            JobModel searchJob = data as JobModel;

            if (searchJob == null)
            {
                try
                {
                    JobApplication tempJobApp = data as JobApplication;
                    searchJob = tempJobApp.Job;
                }
                catch (Exception ex)
                {
                    msg.Result = false;
                    msg.Data = ex.Message;
                }
            }


            msg = new Message();

            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            if (searchJob.Title != null)
            {
                mCommand.CommandText = string.Format("SELECT * FROM Jobs WHERE JobTitle = '{0}' AND Active = 'True'", searchJob.Title);
            }
            else
            {
                mCommand.CommandText = string.Format("SELECT * FROM Jobs WHERE JobID = {0} AND Active = 'True'", searchJob.ID);
            }

            SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);

            try
            {
                mConnection.Open();
                mAdapter.Fill(mTable);
                mConnection.Close();

                if (mTable.Rows.Count != 0)
                {

                    DataRow row = mTable.Rows[0];

                    JobModel job = new JobModel();
                    job.ID = int.Parse(row["JobID"].ToString());
                    job.Title = row["JobTitle"].ToString();
                    job.Location = row["JobLocation"].ToString();
                    job.Salary = row["JobSalary"].ToString();
                    job.Description = row["JobDescription"].ToString();
                    job.PositionsAvailable = int.Parse(row["PositionsAvailable"].ToString());

                    msg.Data = job;
                    msg.Result = true;
                }
                else
                {
                    msg.Result = false;
                    msg.Data = new String(string.Format("Account does not exist.\nUsername/Password miss match").ToCharArray());
                }
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;   
        }


        public override Message SearchForAllData(Message msg)
        {
            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format("SELECT * FROM Jobs WHERE Active = 'True'");
            SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);

            try
            {
                mConnection.Open();
                mAdapter.Fill(mTable);
                mConnection.Close();

                if (mTable.Rows.Count != 0)
                {
                    msg.Result = true;
                    msg.Data = new List<JobModel>();

                    foreach (DataRow row in mTable.Rows)
                    {
                        JobModel job = new JobModel();
                        job.ID = int.Parse(row["JobID"].ToString());
                        job.Title = row["JobTitle"].ToString();
                        job.Location = row["JobLocation"].ToString();
                        job.Salary = row["JobSalary"].ToString();
                        job.Description = row["JobDescription"].ToString();
                        job.PositionsAvailable = int.Parse(row["PositionsAvailable"].ToString());
                        ((List<JobModel>)msg.Data).Add(job);
                    }
                }
                else
                {
                    msg.Result = false;
                    msg.Data = new String(string.Format("Account does not exist.\nUsername/Password miss match").ToCharArray());
                }
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;            
        }


        public override Message EditSpecificData(Message msg, Object data)
        {
            mTable = new DataTable();
            JobModel job = data as JobModel;
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format("UPDATE Jobs SET JobTitle='{0}', JobLocation='{1}', JobSalary='{2}', JobDescription='{3}', PositionsAvailable={4} WHERE JobID={5}",
                                                 job.Title, job.Location, job.Salary, job.Description, job.PositionsAvailable, job.ID);

            try
            {
                mConnection.Open();
                mCommand.ExecuteNonQuery();
                mConnection.Close();
                msg.Result = true;
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }
            return msg;
        }


        public override Message DeleteSpecificData(Message msg, Object data)
        {
            JobModel job = (JobModel)data;
            mCommand = mConnection.CreateCommand();

            mCommand.CommandText = string.Format("DELETE FROM Jobs Where JobTitle = '{0}' AND Active = 'True'", job.Title);

            try
            {
                mConnection.Open();
                mCommand.ExecuteNonQuery();
                mConnection.Close();
                msg.Result = true;
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;
        }


        public override Message DeleteAllData(Message msg)
        {
            throw new NotImplementedException();
        }

        #endregion            
    }
}
